﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

using FbKPI.Model;
namespace FbKPI.Controller
{
    public class SetupYearController : FbKPI.Controller.dbConnection.ConnectionClass
    {
        public string Save(SetupYear SetupYear)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_InsertUpdateSetupYear";
            command.Parameters.Add("@YearID", SqlDbType.VarChar).Value = SetupYear.YearID;
            command.Parameters.Add("@StartYear", SqlDbType.VarChar).Value = SetupYear.StartYear;
            command.Parameters.Add("@EndYear", SqlDbType.VarChar).Value = SetupYear.EndYear;
            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = SetupYear.LastModifiedBy;
            command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = SetupYear.CreateBy;

            return ExecuteNonQuery(command);
        }

        //public string Delete(Section Sections)
        //{
        //    SqlCommand command = new SqlCommand();
        //    command.CommandType = CommandType.StoredProcedure;
        //    command.CommandText = "UP_DELETESECTION";
        //    command.Parameters.Add("@ID", SqlDbType.VarChar).Value = Sections.ID;
        //    command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = Sections.LastModifiedBy;

        //    return ExecuteNonQuery(command);
        //}

        //public string BeforeDelete(Section Sections)
        //{
        //    SqlCommand command = new SqlCommand();
        //    command.CommandType = CommandType.StoredProcedure;
        //    command.CommandText = "SP_CheckBeforeDeleteSection";
        //    command.Parameters.Add("@ID", SqlDbType.Int).Value = Sections.ID;


        //    return GetSingleData(command);
        //}

        public IList<SetupYear> GetAll(string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllSetupYear";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;

            DataSet dx = GetTable(command);
            List<SetupYear> SetupYear = new List<SetupYear>();

            //switch (SortDirection)
            //{
            //    case "asc":
            //        switch (SortCol)
            //        {
            //            case "YearID":
            //                dx.Tables[0].DefaultView.Sort = "YearID ASC";
            //                break;
            //            case "StartYear":
            //                dx.Tables[0].DefaultView.Sort = "StartYear ASC";
            //                break;
            //            case "StartYear":
            //                dx.Tables[0].DefaultView.Sort = "StartYear ASC";
            //                break;
            //            case "LastModifiedDate":
            //                dx.Tables[0].DefaultView.Sort = "LastModifiedDate ASC";
            //                break;
            //            case "LastModifiedBy":
            //                dx.Tables[0].DefaultView.Sort = "LastModifiedBy ASC";
            //                break;
            //            case "CreateDate":
            //                dx.Tables[0].DefaultView.Sort = "CreateDate ASC";
            //                break;
            //            case "CreateBy":
            //                dx.Tables[0].DefaultView.Sort = "CreateBy ASC";
            //                break;
            //        }
            //        break;
            //    case "desc":
            //        switch (SortCol)
            //        {
            //            case "Description":
            //                dx.Tables[0].DefaultView.Sort = "Description DESC";
            //                break;
            //            case "Department":
            //                dx.Tables[0].DefaultView.Sort = "DepartmentID DESC";
            //                break;
            //            case "LastModifiedDate":
            //                dx.Tables[0].DefaultView.Sort = "LastModifiedDate DESC";
            //                break;
            //            case "LastModifiedBy":
            //                dx.Tables[0].DefaultView.Sort = "LastModifiedBy DESC";
            //                break;
            //            case "CreateDate":
            //                dx.Tables[0].DefaultView.Sort = "CreateDate DESC";
            //                break;
            //            case "CreateBy":
            //                dx.Tables[0].DefaultView.Sort = "CreateBy DESC";
            //                break;
            //        }
            //        break;
            //}

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        SetupYear p = new SetupYear();
                        p.YearID = Convert.ToInt16(dt.Rows[i]["YearID"].ToString());
                        p.StartYear = Convert.ToInt16(dt.Rows[i]["StartYear"].ToString());
                        p.EndYear = Convert.ToInt16(dt.Rows[i]["EndYear"].ToString());
                        p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                        SetupYear.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dt.Rows.Count;

            return SetupYear;
        }


        public SetupYear GetByID(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetSetupYearByID";
            command.Parameters.Add("@YearID", SqlDbType.Int).Value = ID;

            IList<string> Data = GetSingleRow(command);
            SetupYear SetupYear = null;

            if (Data != null && Data.Count > 0)
            {
                SetupYear = new SetupYear();
                SetupYear.YearID = Convert.ToInt16(Data[0]);
                SetupYear.StartYear = Convert.ToInt16(Data[1]);
                SetupYear.EndYear = Convert.ToInt16(Data[2]);
                SetupYear.LastModifiedDate = Data[3] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[3].ToString());
                SetupYear.LastModifiedBy = Data[4].ToString();
                SetupYear.CreateDate = Data[5] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[5].ToString());
                SetupYear.CreateBy = Data[6].ToString();
            }

            return SetupYear;
        }

        
    }
}